﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Text;
using OfficeOpenXml;

namespace Practice
{
    public partial class Excel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            gvDemo.DataSource = BindData();
            gvDemo.DataBind();
        }

        /// <summary>
        /// 绑定数据
        /// </summary>
        public DataTable BindData()
        {
            DataTable MyDataTable = new DataTable();
            MyDataTable.Columns.Add(new DataColumn("ID", typeof(string)));
            MyDataTable.Columns.Add(new DataColumn("Name", typeof(string)));
            MyDataTable.Columns.Add(new DataColumn("Sex", typeof(string)));
            MyDataTable.Columns.Add(new DataColumn("BirthDay", typeof(string)));
            MyDataTable.Columns.Add(new DataColumn("家庭住址", typeof(string)));

            DataRow dr;
            for (int i = 0; i <= 10; i++)
            {
                dr = MyDataTable.NewRow();
                dr["ID"] = i;
                dr["Name"] = "222";
                dr["Sex"] = "man";
                dr["BirthDay"] = "555";
                dr["家庭住址"] = "666";

                MyDataTable.Rows.Add(dr);
            }

            return MyDataTable;
        }

        #region EPPlus 方式;

        /// <summary>
        /// EPPlus导出Excel
        /// </summary>
        /// <param name="gv">GridView数据</param>
        /// <param name="name">保存名字</param>
        public void Export(GridView gv, string name)
        {
            ExcelPackage ep = new ExcelPackage();
            ExcelWorkbook wb = ep.Workbook;
            ExcelWorksheet ws = wb.Worksheets.Add("我的工作表");           

            #region 配置文件属性

            //wb.Properties.Category = "类别";
            //wb.Properties.Author = "作者";
            //wb.Properties.Comments = "备注";
            //wb.Properties.Company = "公司";
            //wb.Properties.Keywords = "关键字";
            //wb.Properties.Manager = "管理者";
            //wb.Properties.Status = "内容状态";
            //wb.Properties.Subject = "主题";
            //wb.Properties.Title = "标题";
            //wb.Properties.LastModifiedBy = "最后一次保存者";

            #endregion

            //标题首部
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                ws.Cells[1, i + 1].Value = gv.Columns[i].HeaderText;
            }
            int j = 2;
            foreach (DataRow dr in ((DataTable) gv.DataSource).Rows)
            {
                for (int k = 1; k < gvDemo.Columns.Count + 1; k++)
                {
                    ws.Cells[j, k].Value = dr[k].ToString();
                }
                j++;
            }

            //写到客户端（下载）  
            Response.Clear();
            string filename = "attachment; filename=" + name + ".xls";
            Response.AddHeader("content-disposition", filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(ep.GetAsByteArray());
            //ep.SaveAs(Response.OutputStream); 第二种方式  
            Response.Flush();
            Response.End();
        }

        protected void btn_EPPlus_Export_OnClick(object sender, EventArgs e)
        {
            string name = "ExcelDemo";
            Export(gvDemo, name);
        }

        /// <summary>
        /// 导入Excel到DataTable
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button6_Click(object sender, EventArgs e)
        {
            //string pfilePath = this.txtFilepath.Text.Trim();

            string pfilePath = ""; //Excel文件路径
            if (string.IsNullOrEmpty(pfilePath))
            {
                return;
            }
            FileInfo existingFile = new FileInfo(pfilePath);

            try
            {
                ExcelPackage package = new ExcelPackage(existingFile);
                int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

                ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; //选定 指定页

                int maxColumnNum = worksheet.Dimension.End.Column; //最大列
                int minColumnNum = worksheet.Dimension.Start.Column; //最小列

                int maxRowNum = worksheet.Dimension.End.Row; //最小行
                int minRowNum = worksheet.Dimension.Start.Row; //最大行

                DataTable vTable = new DataTable();
                DataColumn vC;
                for (int j = 1; j <= maxColumnNum; j++)
                {
                    vC = new DataColumn("A_" + j, typeof (string));
                    vTable.Columns.Add(vC);
                }
                if (maxRowNum > 200)
                {
                    maxRowNum = 200;
                }
                for (int n = 1; n <= maxRowNum; n++)
                {
                    DataRow vRow = vTable.NewRow();
                    for (int m = 1; m <= maxColumnNum; m++)
                    {
                        vRow[m - 1] = worksheet.Cells[n, m].Value;
                    }
                    vTable.Rows.Add(vRow);
                }
                //this.dataGridView1.DataSource = vTable;
            }
            catch (Exception vErr)
            {
            }
        }

        #endregion

        #region 快速方法;

        /// <summary>
        /// 重载，否则出现“类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标... ”的错误
        /// </summary>
        /// <param name="control"></param>
        public override void VerifyRenderingInServerForm(Control control)
        {
            //base.VerifyRenderingInServerForm(control);
        }

        /// <summary>
        /// 导出方法
        /// </summary>
        /// <param name="GridView"></param>
        /// <param name="filename">保存的文件名称</param>
        private void ExportGridViewForUTF8(GridView GridView, string filename)
        {
            string attachment = "attachment; filename=" + filename + "_" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";

            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", attachment);

            Response.Charset = "UTF-8";
            Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();

            //用于导出隐藏的GridView的情况;
            bool isHidden = false;
            if (false == GridView.Visible)
            {
                GridView.Visible = true;
                isHidden = true;
            }

            HtmlTextWriter htw = new HtmlTextWriter(sw);
            GridView.RenderControl(htw);

            //用于导出隐藏的GridView的情况;
            if (isHidden)
            {
                GridView.Visible = false;
            }

            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

        protected void btn_Quick_Export_OnClick(object sender, EventArgs e)
        {
            string fileName = "ExcelDemo";
            ExportGridViewForUTF8(gvDemo, fileName);
        }

        #endregion
    }
}